import { sqlUtil } from '../common/db.js';

const GroupModel = {
  // 获取所有群组
  async getList(query, page) {
    let sql = `
      SELECT g.*, s.Name AS AdminName, COUNT(DISTINCT m2.StudentID) AS MemberCount,
             CASE WHEN m1.StudentID IS NOT NULL THEN 1 ELSE 0 END as IsMember
      FROM Groups g
      LEFT JOIN Students s ON g.AdminID = s.StudentID
      LEFT JOIN Members m1 ON g.GroupID = m1.GroupID AND m1.StudentID = ? AND m1.IsDeleted = 0
      LEFT JOIN Members m2 ON g.GroupID = m2.GroupID AND m2.IsDeleted = 0
      WHERE g.IsDeleted = 0
    `;
    let params = [query.studentId];  // 当前用户ID
    
    // 添加搜索条件
    if (query.keyword) {
      sql += ` AND (
        LOWER(g.GroupName) LIKE ? OR 
        LOWER(g.Description) LIKE ? OR
        LOWER(g.Type) LIKE ?
      )`;
      const keyword = `%${query.keyword.toLowerCase()}%`;
      params.push(keyword, keyword, keyword);
    }

    // 处理类型筛选
    if (query.types) {
      // 将逗号分隔的字符串转换为数组
      const types = query.types.split(',').filter(type => type.trim());
      if (types.length > 0) {
        const placeholders = types.map(() => '?').join(',');
        sql += ` AND g.Type IN (${placeholders})`;
        params.push(...types);
      }
    }
    
    sql += ` GROUP BY g.GroupID ORDER BY g.GroupID DESC`;
    return sqlUtil.getLimit(sql, params, page);
  },

  // 根据ID获取群组
  async getById(groupId) {
    const query = `SELECT * FROM Groups WHERE GroupID = ? AND IsDeleted = 0`;
    return sqlUtil.get(query, [groupId]);
  },

  // 创建群组
  async insert(group) {
    const query = `INSERT INTO Groups (GroupName, Description, Type, Privacy, AdminID, IsDeleted)
                  VALUES (?, ?, ?, ?, ?, 0)`;
    return sqlUtil.run(query, [
      group.name,
      group.description,
      group.type,
      group.privacy,
      group.adminId
    ]);
  },

  // 更新群组
  async update(groupId, group) {
    const query = `UPDATE Groups 
                  SET GroupName = ?, Description = ?, Type = ?, Privacy = ?
                  WHERE GroupID = ? AND IsDeleted = 0`;
    return sqlUtil.run(query, [
      group.name,
      group.description,
      group.type,
      group.privacy,
      groupId
    ]);
  },

  // 删除群组(软删除)
  async delete(groupId) {
    const query = `UPDATE Groups SET IsDeleted = 1 WHERE GroupID = ?`;
    return sqlUtil.run(query, [groupId]);
  },

  // 检查用户是否是群组成员
  async isMember(groupId, studentId) {
    const query = `SELECT * FROM Members WHERE GroupID = ? AND StudentID = ? AND IsDeleted = 0`;
    const result = await sqlUtil.get(query, [groupId, studentId]);
    return !!result;
  },
  

  // 获取群组成员列表，包含创建者
  async getMembers(groupId) {
    const query = `SELECT s.Name, s.Photo, m.IsAdmin
                  FROM (
                    SELECT AdminID as StudentID, 1 as IsAdmin, null as JoinDate
                    FROM Groups
                    WHERE GroupID = ? AND IsDeleted = 0
                    UNION
                    SELECT StudentID, 0 as IsAdmin, JoinDate
                    FROM Members 
                    WHERE GroupID = ? AND IsDeleted = 0
                  ) m
                  JOIN Students s ON m.StudentID = s.StudentID
                  ORDER BY m.IsAdmin DESC, m.JoinDate ASC`;
    return sqlUtil.all(query, [groupId, groupId]);
  },

  // 加入群组
  async joinGroup(groupId, studentId) {
    const query = `INSERT INTO Members (GroupID, StudentID, JoinDate, IsDeleted) 
                  VALUES (?, ?, datetime('now'), 0)`;
    return sqlUtil.run(query, [groupId, studentId]);
  },

  // 退出群组
  async leaveGroup(groupId, studentId) {
    const query = `
      UPDATE Members
      SET IsDeleted = 1 
      WHERE GroupID = ? AND StudentID = ?
    `;
    return sqlUtil.run(query, [groupId, studentId]);
  }
};

export default GroupModel;